<?php
defined( "AZ_EXE_" ) or die ( "CORE VARIABLE IS NOT DEFINED." );
	class MenuModel extends Model {
		private $queryGetRow = "";
		public function display( $start=0 , $perpage=20 , $searchString="" , $searchColumns = array("name_en","name_kh" , "menutype" ) , $sortColumn="menutype,ordering" , $sortCriteria="ASC" ){
			
			$start = is_numeric($start)&&$start>-1?$start:0;
			$perpage = is_numeric($perpage)&&$perpage>-1?$perpage:20;
			
			$result = array();
			$query = "SELECT id , menutype , name_kh , name_en , ordering,published,homepage FROM az_menu " ;
			if ( $searchString != "" ){
				$searchColumns = ( $searchColumns===false || ( is_array($searchColumns) && empty($searchColumns) ) ) ? array("name_en","name_kh" , "menutype" ) : $searchColumns ; 
				if ( is_array($searchColumns) && !empty($searchColumns) ){
				$conditions = "" ;
					$conditions = array() ;
					foreach( $searchColumns AS $columnName ){
						$conditions[] = " $columnName LIKE '%$searchString%' " ;
					}
					$conditions = " WHERE " . implode("OR", $conditions );
					$sortColumn = $sortColumn===""||$sortColumn===false?"name_en":$sortColumn;
					$sortCriteria = $sortCriteria===""||$sortCriteria===false?"ASC":$sortCriteria;
					$query .= "$conditions " ;
				}
			}
			if( $sortColumn != "" || $sortColumn != false ){
				$sortCriteria = $sortCriteria!=""&&$sortCriteria!=false?$sortCriteria:"ASC";
				$query .= " ORDER BY $sortColumn $sortCriteria " ;
			}
			$this->queryGetRow = $query ;
			$query .= " LIMIT ".($start*$perpage)." , ".$perpage."; " ;
			$result = $this->executeQueryAsObject( $query ) ;
			return $result;
		}
		public function getMenuFront(){
			$query = "SELECT * FROM az_menu WHERE published = 1 AND menutype='Section Header 2' ORDER BY ordering ASC ";
			return $this->executeQueryAsObject($query);
		}
		public function getTotalRecords(){
			return $this->getRowWithQuery($this->queryGetRow) ;
		}
		
		public function edit($id = 0){
			return $this->executeQueryAsObject("SELECT * FROM az_menu WHERE `id` = '" . $id . "'");
		}
		
		public function add($data = array()){
			$fields = "";
			foreach($data as $table_name => $all_fields){
				$query = "INSERT INTO " . $table_name . " SET ";
				foreach($all_fields as $field_name => $value){
					$fields .= "`" . $field_name . "` = '" . $this->sqlRealEscape($value) . "',";
					//print_r($table_name);
				}
				$query .= $fields . " `ordering` = (SELECT MAX(m.`ordering`) + 1 FROM az_menu as m), `created` = NOW(), `modified` = NOW()";//die($query);
				return $this->executeQuery($query);
			}
		}
		
		public function getMenuType(){
			return $this->executeQueryAsObject("SELECT * FROM az_menu_types WHERE `id` in (4,8)");
		}
		
		public function update($data = array()){
			$fields = "";
			foreach($data as $table_name => $all_fields){
				$query = "UPDATE " . $table_name . " SET ";
				foreach($all_fields as $field_name => $value){
					$fields .= "`" . $field_name . "` = '" . $this->sqlRealEscape($value) . "',";
					//print_r($table_name);
				}
				$query .= $fields . " `modified` = NOW() WHERE `id` = " . $data[$table_name]['id'];//die($query);
				return $this->executeQuery($query);
			}
		}
		public function delete($id = 0){
			$id = is_array($id)&&!empty($id)?implode(",",$id):$id;
			return $this->executeQuery("DELETE FROM az_menu WHERE `id` in (" . $id . ")");
		}
		public function find( $id ){
			return $this->executeQueryAsObject("SELECT * FROM az_menu WHERE id = '$id' ;");
		}
		/* front page */
		public function getMenu($section_menu = "Section Menues", $lang = "en"){
			$lang = $_SESSION['lang'];
			$query = "";
			$query2 = "";
			if($lang == "en"){
				$query = "
					SELECT m.`id` as menu_id,m.`name_en` as menu_name,m.`alias`,m.`link`
				";	
			}elseif($lang == "kh"){
				$query = "
					SELECT m.`id` as menu_id,m.`name_kh` as menu_name,m.`alias`,m.`link`
				";	
			}
			$query .= "
					FROM `az_menu` as m
					WHERE m.`published` = 1 AND m.`menutype` = '" . $section_menu . "' AND m.`name_en` <> 'SMECA\'s Channels'
					ORDER BY m.`ordering`, m.`name_en` ASC
				
			";
			$menus = $this->executeQueryAsObject($query);
			// category
			if($lang == "en"){
				$query2 = "
				SELECT m.`id` as menu_id, c.`id` as cate_id,c.`title_en` as category_title,c.`alias`
				";
			}elseif($lang == "kh"){
				$query2 = "
				SELECT m.`id` as menu_id, c.`id` as cate_id,c.`title_kh` as category_title ,c.`alias`
				";
			}
			$query2 .= "
			FROM `az_menu` as m LEFT OUTER JOIN `az_categories` as c ON m.`id` = c.`menu_id`
			WHERE c.`published` = 1 AND m.`menutype` ='" . $section_menu . "'
			ORDER BY c.`ordering` ASC
			";
			$categories = $this->executeQueryAsObject($query2);
			$menu_lists = array();
			foreach($menus as $menu){
				$cate_lists = array();
				foreach($categories as $cate){
					if($cate -> menu_id == $menu -> menu_id){
						$cate_lists[$cate->cate_id . "||" . $cate->alias] = $cate->category_title;
					}
				}
				if(count($cate_lists)){
					$menu_lists[$menu->menu_id . "||" . $menu->alias . "||" . $menu->link][$menu->menu_name] = $cate_lists;
				}
				else{
					$menu_lists[$menu->menu_id . "||" . $menu->alias . "||" . $menu->link] = $menu->menu_name;
				}
			}//print_r($menu_lists);die();
			return $menu_lists;
		}
		public function getMenus($section_menu = "Section Menues", $lang = "en"){
			$lang = $_SESSION['lang'];
			if($lang == "en"){
				$query = "
				SELECT m.id as menu_id,m.name_en as menu_name,m.alias , m.link as link
				";
			}elseif($lang == "kh"){
				$query = "
				SELECT m.id as menu_id,m.name_kh as menu_name ,m.alias, m.link as link
				";
			}
			$query .= "
			FROM az_menu as m 
			WHERE `published` = 1
			AND `menutype` ='".$section_menu."' ;";
			
			return $this->executeQueryAsObject($query);
		}
		public function getCategory($mid = 0){
			$lang = $_SESSION['lang'];
			if($lang == "en"){
				$query = "
				SELECT m.`id` as menu_id, m.`name_en` as menu_name, c.`id` as cate_id,c.`title_en` as category_title
				";
			}elseif($lang == "kh"){
				$query = "
				SELECT m.`id` as menu_id, m.`name_kh` as menu_name, c.`id` as cate_id,c.`title_kh` as category_title
				";
			}
			$query .= "
			FROM `az_menu` as m LEFT OUTER JOIN `az_categories` as c ON m.`id` = c.`menu_id`
			WHERE c.`published` = 1 AND m.`id` = $mid
			ORDER BY c.`ordering` ASC
			";
			$categories = $this->executeQueryAsObject($query);
			$tmps = array();
			if(count($categories) > 0){
				foreach($categories as $cat){
					$menu_name = $cat->menu_name;
					if($lang == "en"){
						$query2 = "
						SELECT a.`id` as article_id,a.`title_en` as article_title, a.`introtext_en` as article_introtext, a.`created`, a.is_thumbnail, a.image_thumbnail,
						(SELECT `description_en` FROM `az_categories` WHERE `show_description` = 1 AND `id` = '$cat->cate_id') as cate_description,
						(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
						";
					}elseif($lang == "kh"){
						$query2 = "
						SELECT a.`id` as article_id,a.`title_kh` as article_title, a.`introtext_kh` as article_introtext, a.`created`, a.is_thumbnail, a.image_thumbnail,
						(SELECT `description_kh` FROM `az_categories` WHERE `show_description` = 1 AND `id` = '$cat->cate_id') as cate_description,
						(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
						";
					}
					$query2 .= "
					FROM `az_articles` as a RIGHT JOIN `az_categories` as c ON a.`catid` = c.`id`
					WHERE c.`published` = 1 AND a.`published` = 1 AND c.`id` = $cat->cate_id
					ORDER BY a.`created` DESC
					";
					//die($query2);
					$articles = $this->executeQueryAsObject($query2);
					$tmps[$cat->cate_id] = array($cat->category_title => $articles);
				}
			}
			return array($tmps,$menu_name);;
		}
		public function getMenuHomepage($limit = 5, $page = 1){			
			$page = isset($_SESSION['nav']['page-article-by-menu'])?$_SESSION['nav']['page-article-by-menu']:$page;
			$page = $page<1?1:$page;
			$lang = $_SESSION['lang'];
			if($lang == "en"){
				$query = "
				SELECT m.`id` as menu_id,m.`name_en` as menu_name, c.`id` as cate_id,c.`title_en` as cate_name
				";
			}elseif($lang == "kh"){
				$query = "
				SELECT m.`id` as menu_id,m.`name_kh` as menu_name, c.`id` as cate_id,c.`title_kh` as cate_name
				";
			}
			$query .= "
			FROM `az_menu` as m LEFT JOIN `az_categories` as c ON m.`id` = c.`menu_id`
			WHERE m.`published` = 1 AND m.`homepage` = 1
			ORDER BY m.`ordering` ASC
			";
			$menus = $this->executeQueryAsObject($query);//Basic::pr($menus);die();
			$tmp_menus = array();
			foreach ($menus as $menu){
				$menu_id = $menu->menu_id;
				$menu_name = $menu->menu_name;
				$cate_id = $menu->cate_id;
				$tmp_menus[$menu_id . "||" . $menu_name][$cate_id] = $menu; 
			}
			//Basic::pr($tmp_menus);die();
			$tmp_articles = array();
			if(count($tmp_menus) > 0){
				foreach($tmp_menus as $menu_info => $categories){
					list($menu_id,$menu_name) = explode("||", $menu_info);
					
					if($lang == "en"){
						$query2 = "
						SELECT a.`id` as article_id,a.`title_en` as article_title, a.`introtext_en` as article_introtext, a.`created`, a.is_thumbnail, a.image_thumbnail, c.`id` as cate_id, c.`title_en` as cate_name,					
						(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
						";
					}elseif($lang == "kh"){
						$query2 = "
						SELECT a.`id` as article_id,a.`title_kh` as article_title, a.`introtext_kh` as article_introtext, a.`created`, a.is_thumbnail, a.image_thumbnail, c.`id` as cate_id, c.`title_kh` as cate_name,
						(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
						";
					}
					$query2 .= "
					FROM `az_articles` as a RIGHT JOIN `az_categories` as c ON a.`catid` = c.`id` INNER JOIN `az_menu` as m ON m.`id` = c.`menu_id`
					WHERE c.`published` = 1 AND a.`published` = 1 AND c.`menu_id` = $menu_id
					ORDER BY a.`created` DESC ";
                    $total = ceil($this->getRowWithQuery($query2)/$limit);
                    if(@$_SESSION['nav']['menuid-article-by-menu'] == $menu_id){
						$query2 .= "LIMIT " .(($page-1)*$limit). "," . $limit;
                    }else{
                    	$query2 .= "LIMIT 0," . $limit;
                    }
					$articles = array("page"=>$page,"total"=>$total,"articles"=>$this->executeQueryAsObject($query2));
					if(count($articles)){
						$tmp_articles[$menu_info] = $articles;
					}
				}
			}//die(Basic::pr($tmp_articles));
			return array($tmp_menus,$tmp_articles);
		}
		public function getCategoryHomepage($limit = 5){
			$lang = $_SESSION['lang'];
			if($lang == "en"){
				$query = "
				SELECT m.`id` as menu_id,m.`name_en` as menu_name, c.`id` as cate_id,c.`title_en` as category_title
				";
			}elseif($lang == "kh"){
				$query = "
				SELECT m.`id` as menu_id,m.`name_kh` as menu_name, c.`id` as cate_id,c.`title_kh` as category_title
				";
			}
			$query .= "
			FROM `az_menu` as m LEFT JOIN `az_categories` as c ON m.`id` = c.`menu_id`
			WHERE c.`published` = 1 AND c.`homepage` = 1
			ORDER BY c.`ordering` ASC
			";
			$categories = $this->executeQueryAsObject($query);//Basic::pr($categories);die();
			$tmps = array();
			if(count($categories) > 0){
				foreach($categories as $cat){
					if($lang == "en"){
						$query2 = "
						SELECT a.`id` as article_id,a.`title_en` as article_title, a.`introtext_en` as article_introtext, a.`created`, a.is_thumbnail, a.image_thumbnail,
						(SELECT `description_en` FROM `az_categories` WHERE `show_description` = 1 AND `id` = '$cat->cate_id') as cate_description,
						(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
						";
					}elseif($lang == "kh"){
						$query2 = "
						SELECT a.`id` as article_id,a.`title_kh` as article_title, a.`introtext_kh` as article_introtext, a.`created`, a.is_thumbnail, a.image_thumbnail,
						(SELECT `description_kh` FROM `az_categories` WHERE `show_description` = 1 AND `id` = '$cat->cate_id') as cate_description,
						(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
						";
					}
					$query2 .= "
					FROM `az_articles` as a RIGHT JOIN `az_categories` as c ON a.`catid` = c.`id`
					WHERE c.`published` = 1 AND a.`published` = 1 AND c.`id` = $cat->cate_id
					ORDER BY a.`created` DESC LIMIT $limit
					";
					$articles = $this->executeQueryAsObject($query2);
					$tmps[$cat->menu_id . "||" . str_replace(";", "",$cat->menu_name) . "||" .$cat->cate_id] = array($cat->category_title => $articles);
				}
			}
			return $tmps;
		}
		public function getCategoryContactUs(){
			$lang = $_SESSION['lang'];
			$query = "";
			if($lang == "en"){
				$query = "
				SELECT `title_en` as cate_title, `description_en` as description FROM `az_categories` WHERE `show_description` = 1 AND `alias` = 'contact-us'
				";
			}elseif($lang == "kh"){
				$query = "
				SELECT `title_kh` as cate_title, `description_kh` as description FROM `az_categories` WHERE `show_description` = 1 AND `alias` = 'contact-us'
				";
			}
			$category = $this->executeQueryAsObject($query);
			return $category[0];
		}
	}
?>